/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');
var async  = require( 'async'); // 同步控制


// Schema 结构
var ItbQtyPriceModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`price_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '价格id',
  //`pp_id` bigint(20) NOT NULL COMMENT '印刷参数id',
  //`price` bigint(20) NOT NULL COMMENT '价格（单位分）',
  //`unit` varchar(20) NOT NULL DEFAULT '' COMMENT '计量单位',
  //`min` bigint(20) NOT NULL COMMENT '数量最小值（界面显示取最小值）',
  //`max` bigint(20) NOT NULL COMMENT '数量最大值',
  //`show` varchar(20) DEFAULT NULL COMMENT '数量显示值',
  //`state` int(2) NOT NULL DEFAULT '0' COMMENT '状态',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`del_flg` int(1) NOT NULL DEFAULT '0',

};
ItbQtyPriceModel.prototype = new ItbBaseModel();

// 取得表名
ItbQtyPriceModel.prototype.getTableName = function(){
    return 'itb_qty_price';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbQtyPriceModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'price_id='+this.price_id;
}

// 获取某个印刷参数／印刷套餐的数量价格列表
// 参数: ppId     印刷参数／印刷套餐id
// 参数: pageNum  请求页
// 参数: pageSize 请求页大小(为空或0时获取所有数据)
// 参数: callback 回调，参数如下：
//          error:错误
//          result:多行数据
ItbQtyPriceModel.prototype.findQtyPriceByPrintParamId = function(ppId, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbQtyPriceModel.findQtyPriceByItemId Err:'+err);
        }
        // Use the connection
        //var sql = ' select *  '+
        //    ' from itb_qty_price qp '+
        //    ' where qp.`del_flg`=0 '+
        //    ' and qp.`pp_id`= '+ppId+
        //    ' order by qp.min asc ';
        //if(Number(pageSize||'0')>0){
        //    var skipCnt = Number(pageNum||0)*Number(pageSize);
        //    sql += ' limit '+skipCnt+','+Number(pageSize);
        //}

        pageSize =6;
        var sql = ' call proc_find_qty_price('+ppId+','+pageSize+') ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(result && result.length>0){
                return callback(error, result[0]);
            }else{
                return callback(null,null);
            }


        });
    });
}

// 获取印品的最小“价格－数量”数据
// 参数: itemId	 印品id
// 参数：callback 回调，参数如下：
//          error:错误
//          result:QtyPriceModel
ItbQtyPriceModel.prototype.findMinQtyPriceByItemId = function(itemId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbItemsModel.findMinQtyPriceByItemId Err:'+err);
        }

        // Use the connection
        var sql =
            '  			 select qp.*,min(qp.min) '+
            '              from itb_qty_price qp,  '+
            '                   (select qp2.pp_id, min(qp2.min) as valmin from itb_qty_price qp2 where qp2.del_flg=0 group by qp2.pp_id) qp3, '+
            '              		itb_print_param pp,  '+
            '              		itb_print_rel pr '+
            '              where qp.`del_flg`=0  '+
            '              AND qp.pp_id=qp3.pp_id AND qp.min=qp3.valmin '+// 关联qp3取最小价格 added 2017-07-20
            '              and pp.`del_flg`=0  '+
            '              and pr.`del_flg`=0  '+
            '              and qp.`pp_id`= pp.`pp_id`  '+
            '              and qp.`pp_id`= pr.`pp_id`  '+
            '              and pr.`item_id`= '+ itemId;


        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, null);
            }
            if(!result || result.length==0){
                return callback(null,null);
            }

            return callback(error, result[0]);
        });
    });
}

// 获取印品的最小“价格－数量”数据
// 参数: userDocId	 用户文档id
// 参数：callback 回调，参数如下：
//          error:错误
//          result:QtyPriceModel+商品item_id+印刷参数pp_id
ItbQtyPriceModel.prototype.findMinQtyPriceByUserDocId = function(userDocId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbItemsModel.findMinQtyPriceByItemId Err:'+err);
        }

        // Use the connection
        var sql =
            '  			   select qp.*,min(qp.min),dd.`item_id`,pp.`pp_id`  '+
            '              from itb_qty_price qp,  '+
            '              		(SELECT qp2.pp_id, MIN(qp2.min) AS min FROM itb_qty_price qp2 WHERE qp2.del_flg=0 GROUP BY qp2.pp_id) qp3,  '+
            '              		itb_print_param pp,  '+
            '              		itb_print_rel pr, '+
            '              		tg_user_doc ud, '+
            '              		tg_design_doc dd  '+
            '              where ud.`doc_id`= '+ userDocId +
            '              and ud.`design_id`=dd.`design_id` '+
            '              and pr.`item_id`=dd.`item_id` '+
            '              and qp.`pp_id`= pp.`pp_id`  '+
            '              and qp.`pp_id`= pr.`pp_id`  '+
            '              and qp.`pp_id`= qp3.`pp_id` AND qp.min=qp3.min '+
            '              and ud.`del_flg`=0  '+
            '              and dd.`del_flg`=0  '+
            '              and qp.`del_flg`=0  '+
            '      		   and pp.`del_flg`=0  '+
            '              and pr.`del_flg`=0 ';


        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, null);
            }
            if(!result || result.length==0){
                return callback(null,null);
            }

            return callback(error, result[0]);
        });
    });
}

// 根据套餐id和数量获取价格信息
ItbQtyPriceModel.prototype.getPriceByPpIdAndQty = function(ppId, qty, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbItemsModel.getPriceByPpIdAndQty Err:'+err);
            return callback(err,null);
        }

        // Use the connection
        var sql = ' SELECT t.price_id, t.pp_id, t.min, t.max, t.show, t.unit, t.weight '+
            '             ,pp.pricing, t.price '+
            '             ,CASE WHEN pp.pricing=20 THEN t.price*'+qty+' ELSE t.price END AS total_price '+
            ' FROM itb_qty_price t, itb_print_param pp '+
            ' WHERE t.del_flg=0 AND t.state=10 AND t.pp_id='+ppId+
            ' AND pp.del_flg=0 AND pp.pp_id=t.pp_id '+
            ' AND ( '+
            '       (pp.pricing=10 AND t.min<='+qty+' AND t.max>='+qty+') OR '+// 区间计价
            '       (pp.pricing=20 AND t.min=1) '+// 单价
            ' )';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, null);
            }
            if(!result || result.length==0){
                return callback(null,null);
            }

            return callback(error, result[0]);
        });
    });
}

// 导出
module.exports = ItbQtyPriceModel;